﻿using FRMS.Helper;
using FRMS.Model;
using System;
using System.Collections.Generic;

namespace FRMS.DAL
{
    public class AuditDAL
    {
        public List<FrmsAuditInfo> QueryFrmsAuditInfo(FrmsAuditInfo frmsAuditInfo)
        {
            string strSql = "SELECT * FROM vi_audit";
            string sqlwhere = string.Empty;

            if (frmsAuditInfo.projectId > 0)
            {
                sqlwhere += " projectId=" + frmsAuditInfo.projectId + " AND";
            }
            if (!string.IsNullOrEmpty(frmsAuditInfo.Name))
            {
                sqlwhere += " Name like '%" + frmsAuditInfo.Name + "%' AND";
            }
            if (!string.IsNullOrEmpty(frmsAuditInfo.OwnerName))
            {
                sqlwhere += " OwnerName like '%" + frmsAuditInfo.OwnerName + "%' AND";
            }

            if (!string.IsNullOrEmpty(frmsAuditInfo.StartTime))
            {
                sqlwhere += " DATE(applicationTime)>=DATE('" + frmsAuditInfo.StartTime + "') AND";

            }
            if (!string.IsNullOrEmpty(frmsAuditInfo.EndTime))
            {
                sqlwhere += " DATE(applicationTime) <=DATE('" + frmsAuditInfo.EndTime + "') AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            strSql += " ORDER BY auditStatus asc";

            if (frmsAuditInfo.Recordhead > -1 && frmsAuditInfo.Recordtail > 0)
            {
                strSql += " LIMIT " + frmsAuditInfo.Recordhead + "," + frmsAuditInfo.Recordtail;
            }

            return SqlHelper<FrmsAuditInfo>.SqlList(strSql);
        }

        public int QueryFrmsAuditInfoCount(FrmsAuditInfo frmsAuditInfo)
        {
            string strSql = "SELECT Count(1) FROM vi_audit";
            string sqlwhere = string.Empty;

            if (frmsAuditInfo.projectId > 0)
            {
                sqlwhere += " projectId=" + frmsAuditInfo.projectId + " AND";
            }
            if (!string.IsNullOrEmpty(frmsAuditInfo.Name))
            {
                sqlwhere += " Name like '%" + frmsAuditInfo.Name + "%' AND";
            }
            if (!string.IsNullOrEmpty(frmsAuditInfo.OwnerName))
            {
                sqlwhere += " OwnerName like '%" + frmsAuditInfo.OwnerName + "%' AND";
            }

            if (!string.IsNullOrEmpty(frmsAuditInfo.StartTime))
            {
                sqlwhere += " DATE(applicationTime)>=DATE('" + frmsAuditInfo.StartTime + "') AND";

            }
            if (!string.IsNullOrEmpty(frmsAuditInfo.EndTime))
            {
                sqlwhere += " DATE(applicationTime) <=DATE('" + frmsAuditInfo.EndTime + "') AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<WorkModeStateRecord>.SqlScalar(strSql);
        }

        public FrmsAuditInfo LoadFrmsAuditInfo(int id)
        {
            string strSql = "SELECT * FROM vi_audit WHERE id=" + id;
            return SqlHelper<FrmsAuditInfo>.SqlSingleData(strSql);
        }

        public bool UpdateAuditState(FrmsAuditInfo frmsAuditInfo)
        {

            string strSql = "UPDATE auditrecord SET auditStatus=" + frmsAuditInfo.auditStatus + ",syncFlag=0,auditTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE id=" + frmsAuditInfo.id;
            if (frmsAuditInfo.auditStatus == 2)
            {
                strSql += ";UPDATE voucher_info SET QRCodeFlag=1,SyncFlag=0 WHERE OwnerID=" + frmsAuditInfo.ownerId;
            }
            else
            {
                strSql += ";UPDATE voucher_info SET QRCodeFlag=0,SyncFlag=0 WHERE OwnerID=" + frmsAuditInfo.ownerId;
            }
            return SqlHelper<BuildInfo>.SqlExecute(strSql);
        }

        public List<FrmsAuditInfo> QueryOwnerByAudit(FrmsAuditInfo frmsAuditInfo)
        {
            string strSql = "SELECT * FROM vi_owner";
            string sqlwhere = string.Empty;

            if (frmsAuditInfo.projectId > 0)
            {
                sqlwhere += " projectId=" + frmsAuditInfo.projectId + " AND";
            }
            if (!string.IsNullOrEmpty(frmsAuditInfo.Name))
            {
                sqlwhere += " OwnerName like '%" + frmsAuditInfo.Name + "%' AND";
            }
            sqlwhere += " IsDelete=0 AND";
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<FrmsAuditInfo>.SqlList(strSql);
        }

        public bool BindOwner(FrmsAuditInfo frmsAuditInfo)
        {
            string strSql = "UPDATE auditrecord SET ownerId=" + frmsAuditInfo.ownerId + " WHERE id=" + frmsAuditInfo.id;
            return SqlHelper<BuildInfo>.SqlExecute(strSql);
        }

        public List<VoucherInfo> QueryQRCodeVoucher()
        {
            string strSql = "SELECT * FROM vi_voucher  WHERE QRCodeFlag=1 AND SyncFlag=0";
            return SqlHelper<VoucherInfo>.SqlList(strSql);
        }

        public List<FrmsAuditInfo> QueryQRCodeAuditInfo()
        {
            string strSql = "SELECT * from vi_audit WHERE auditStatus!=0 AND syncFlag=0";
            return SqlHelper<FrmsAuditInfo>.SqlList(strSql);
        }

        public bool UpdateAuditSync(int id)
        {
            string strSql = "UPDATE auditrecord SET syncFlag=1 WHERE auditid=" + id;
            return SqlHelper<BuildInfo>.SqlExecute(strSql);
        }

        public bool UpdateVoucherQRCodeFlag(int ownerid)
        {
            string strSql = "UPDATE voucher_info SET QRCodeFlag=1,SyncFlag=0 WHERE OwnerID=" + ownerid;
            return SqlHelper<BuildInfo>.SqlExecute(strSql);
        }

        public bool DeleteAudit(int auditid)
        {
            string strSql = string.Format("UPDATE voucher_info SET QRCodeFlag=0 WHERE OwnerID IN(SELECT ownerId FROM auditrecord WHERE auditid={0});DELETE FROM auditrecord WHERE auditid={0}", auditid);
            return SqlHelper<BuildInfo>.SqlExecute(strSql);
        }

        public List<QRVoucher> QueryQRVoucher(int auditid)
        {
            string strSql = "SELECT * FROM vi_qrvoucher WHERE AuditId=" + auditid;
            string sqlwhere = string.Empty;

            return SqlHelper<QRVoucher>.SqlList(strSql);
        }

        public List<QRVoucher> QueryQRVoucherByDevice(int DeviceId, int auditid)
        {
            string strSql = "SELECT * FROM vi_qrvoucher WHERE AuditId=" + auditid + " AND DeviceId=" + DeviceId;
            string sqlwhere = string.Empty;

            return SqlHelper<QRVoucher>.SqlList(strSql);
        }

        public bool addQRVoucher(QRVoucher qRVoucher)
        {
            string strSql = string.Format("INSERT INTO qr_voucher(AuditId,DeviceId,AuthorizedFloor,EffectiveStarttime,EffectiveEndtime,SyncFlag)VALUES({0},{1},'{2}','{3}','{4}',0)", qRVoucher.AuditId, qRVoucher.DeviceId, qRVoucher.AuthorizedFloor, Convert.ToDateTime(qRVoucher.EffectiveStarttime).ToString("yyyy-MM-dd HH:mm:ss"), Convert.ToDateTime(qRVoucher.EffectiveEndtime).ToString("yyyy-MM-dd HH:mm:ss"));
            return SqlHelper<BuildInfo>.SqlExecute(strSql);
        }

        public bool updateQRVoucher(QRVoucher qRVoucher)
        {
            string strSql = string.Format("update qr_voucher set AuthorizedFloor='{0}',EffectiveStarttime='{1}',EffectiveEndtime='{2}',SyncFlag=0 where AuditId={3} and DeviceId={4}", qRVoucher.AuthorizedFloor, Convert.ToDateTime(qRVoucher.EffectiveStarttime).ToString("yyyy-MM-dd HH:mm:ss"), Convert.ToDateTime(qRVoucher.EffectiveEndtime).ToString("yyyy-MM-dd HH:mm:ss"), qRVoucher.AuditId, qRVoucher.DeviceId);
            return SqlHelper<BuildInfo>.SqlExecute(strSql);
        }

        public bool DeleteQRVoucher(int id)
        {
            string strSql = "DELETE FROM qr_voucher WHERE ID=" + id;
            return SqlHelper<BuildInfo>.SqlExecute(strSql);
        }

        public bool UpdateSyncState(QRCodeVoucherInfoReturnData qRCode)
        {
            string strSql = string.Format(@"UPDATE qr_voucher SET syncFlag = 1 WHERE DeviceID in (SELECT ID FROM device_info WHERE DeviceNO='{0}') AND AuditId ={1}",
                                           qRCode.deviceNo, qRCode.auditid);
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }
    }
}
